# 封装读取excel数据
import openpyxl
from openpyxl import workbook
from collections.abc import Iterable
import os
import sys

# 获取当前目录
BASEDIR = os.path.dirname(os.path.dirname(__file__))

print(BASEDIR)


class HandleExcel:
    def load_excel(self):
        """加载excel"""
        open_excel = openpyxl.load_workbook(BASEDIR + "/data/data.xlsx")
        return open_excel

    def get_sheet_data(self, index=None):
        """加载所有sheet的内容"""
        sheet_name = self.load_excel().sheetnames
        if index == None:
            index = 0
        data = self.load_excel()[sheet_name[index]]
        return data

    def get_cell_value(self, row, cols):
        """获取某一个单元格内容"""
        data = self.get_sheet_data().cell(row=row, column=cols).value
        return data

    def get_rows(self):
        """获取行数"""
        row = self.get_sheet_data().max_row
        return row

    def get_rows_value(self, row):
        """获取某一行的内容"""
        row_list = []
        for i in self.get_sheet_data()[row]:
            row_list.append(i.value)
        return row_list

    def get_columns_value(self, key=None):
        """获取某一列的数据"""
        columns_list = []
        if key == None:
            key = 'A'
        columns_list_data = self.get_sheet_data()[key]
        for i in columns_list_data:
            columns_list.append(i.value)
        return columns_list

    def get_rows_number(self, case_id):
        """获取行号"""
        num = 1
        cols_data = self.get_columns_value()
        for col_data in cols_data:
            if case_id == col_data:
                return num
            num = num + 1
        return num

    def get_excel_data(self):
        """获取excel里面所有的数据"""
        data_list = []
        for i in range(self.get_rows()):
            data_list.append(self.get_rows_value(i + 2))

        return data_list


handle_excel = HandleExcel()
print(handle_excel.get_excel_data())
